package com.ifras.project.control.data;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
	
	//database information
	private static final String DATABASE_NAME = "SpeedDB.db"; 
	private static final int DATABASE_VERSION = 1;
	
	
	//TABLES
	
		//area coordinates table
		public static final String TABLE_AREA_COORDINATES = "area_coordinates_tbl";
		public static final String COORDINATES_ID = "coordinates_id";
		public static final String COORDINATES_LATI = "coordinates_latitude";
		public static final String COORDINATES_LONG = "coordinates_longitude";
		public static final String COORDINATES_AREA_ID = "coordinates_area_id";
		
		//area details table
		public static final String TABLE_AREA_DETAILS = "area_details_tbl";
		public static final String AREA_ID = "area_id";
		public static final String AREA_NAME = "area_name";
		public static final String AREA_SPEED_LIMIT = "area_speed_limit";
		
		//log status table
		public static final String TABLE_LOG_DETAILS = "log_details_tbl";
		public static final String LOG_ID = "log_id";
		public static final String LOG_AREA_ID_NAME = "log_area_id";
		public static final String LOG__TRACKED_MAX_SPEED = "log_tracked_maximun_speed";
		public static final String LOG_CLOSEST_LATITUDE = "log_closest_latitude";
		public static final String LOG_CLOSEST_LONGITUDE = "log_closest_longitude";
		public static final String LOG_DATE_TIME = "log_date_time";
		public static final String LOG_DRIVER_ID = "log_driver_id";
		public static final String LOG_STATUS = "log_status";
		
		
		private static final String CREATE_AREA_COORDINATES_TABLE = "CREATE TABLE " + TABLE_AREA_COORDINATES + " ("
				+ COORDINATES_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ COORDINATES_LATI + " REAL, "
				+ COORDINATES_LONG + " REAL, "
				+ COORDINATES_AREA_ID + " INTEGER);";
		
		
		
		private static final String CREATE_AREA_TABLE = "CREATE TABLE " + TABLE_AREA_DETAILS + " ("
				+ AREA_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ AREA_NAME + " TEXT, "
				+ AREA_SPEED_LIMIT + " REAL);";
		
		private static final String CREATE_LOG_DETAILS_TABLE = "CREATE TABLE " + TABLE_LOG_DETAILS + " ("
				+ LOG_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ LOG_AREA_ID_NAME + " TEXT, "
				+ LOG__TRACKED_MAX_SPEED + " REAL, "
				+ LOG_CLOSEST_LATITUDE + " REAL, "
				+ LOG_CLOSEST_LONGITUDE + " REAL, "
				+ LOG_DATE_TIME + " TEXT, "	
				+ LOG_DRIVER_ID + " INTEGER, "	
				+ LOG_STATUS + " TEXT);";		

		
		public DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}
		
		@Override
		public void onCreate(SQLiteDatabase arg0) { // this order must be followed when creating tables
			arg0.execSQL(CREATE_AREA_COORDINATES_TABLE);
			arg0.execSQL(CREATE_AREA_TABLE);
			arg0.execSQL(CREATE_LOG_DETAILS_TABLE);
			
		}
		
		@Override
		public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
			arg0.execSQL("DROP TABLE IF EXISTS " + CREATE_AREA_COORDINATES_TABLE);
			arg0.execSQL("DROP TABLE IF EXISTS " + CREATE_AREA_TABLE);
			arg0.execSQL("DROP TABLE IF EXISTS " + CREATE_LOG_DETAILS_TABLE);
			onCreate(arg0); 
			
		}
}
